/* This example demonstrates the ins and outs of the HAVING clause 

Business Problem: Brandon runs a uber high end health club.  
He's considering giving a free hug to anyone who has done more than 20 recorded push ups.
	
HAVING Documentation
http://msdn.microsoft.com/en-us/library/ms180199(v=sql.90).aspx

HAVING is available for SQL Server 2005 and above (including 2008, 2008 R2, and 2012)

In general HAVING is used to filter results that have an aggregrate function in the SELECT portion of the statement (AVG, MIN, MAX, SUM...)

In the example below the biggest take away is the removing of the subquery to produce the desired results.  Readability and maintanability goes up because
the code takes a more readable approach.  Notice the column alias "TheCount" cannot be used to reference the column in the HAVING statement.  
The estimated execution plan is identical so SQL server is handling both the subquery and HAVING identically to get the results.
*/


--SETUP Some sample data.
DECLARE @TemporaryTable TABLE (ID INTEGER IDENTITY (1,1) PRIMARY KEY, NumberOfPushUps INTEGER, NAME VARCHAR(25))
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Sue')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Sue')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Sue')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Sue')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Ray')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Ray')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Michael')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Michael')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Jessica')
INSERT INTO @TemporaryTable (NumberOfPushUps,NAME) VALUES (NULL,'Jessica')
UPDATE @TemporaryTable SET NumberOfPushUps = CEILING(ID / 2) * 4

--Look at the sample data
SELECT * FROM @TemporaryTable

--A sample without the HAVING statement.  Find all people who did more than 20 push ups.
SELECT
	*
FROM
	(SELECT
		SUM(NumberOfPushUps) AS TheCount,
		NAME
	 FROM
		@TemporaryTable
	 GROUP BY
		Name) AS T
WHERE
	T.TheCount > 20
ORDER BY
	TheCount ASC
	
--Same query using HAVING. Find all people who did more than 20 push-ups
SELECT
	SUM(NumberOfPushUps) AS TheCount,
	Name
FROM
	@TemporaryTable 
GROUP BY
	NAME
HAVING
	SUM(NumberOfPushUps) > 20
ORDER BY
	TheCount ASC
	 


